package com.cnlbc.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.cnlbc.Model.Book;
import com.cnlbc.Util.StringUtil;

public class BookDao {
	/**
	 * 图书添加
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception 
	 */
	public int addBook(Connection con,Book book) throws Exception {
		String sql="insert into book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2,book.getAuthor());
		pstmt.setString(3,book.getSex());
		pstmt.setFloat(4,book.getPrice());
		pstmt.setInt(5,book.getBookTypeid());
		pstmt.setString(6,book.getBookDesc());
		return pstmt.executeUpdate();
	}
	/**
	 * 图书信息查询
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public ResultSet ListBook(Connection con,Book book) throws Exception {
		StringBuffer sb=new StringBuffer("select * from book b,booktype t where b.bookTypeid=t.id");
		if(StringUtil.isNotEmpty(book.getBookName())) {
			sb.append(" and b.bookName like '%"+book.getBookName()+"%'");
		}
		if(StringUtil.isNotEmpty(book.getAuthor())) {
			sb.append(" and b.author like '%"+book.getAuthor()+"%'"); 
		}
		if(book.getBookTypeid()!=null&&book.getBookTypeid()!=-1) {
			sb.append(" and b.bookTypeid="+book.getBookTypeid()); 
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	/**
	 * 修改图书信息
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception 
	 */
	public int updateBook(Connection con,Book book) throws Exception {
		String sql="update book set bookName=? ,author=?,sex=?,price=?,bookTypeid=?,bookDesc=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(5, book.getBookTypeid());
		pstmt.setString(6, book.getBookDesc());
		pstmt.setInt(7, book.getId());
		return pstmt.executeUpdate();
	}
	/**
	 * 删除图书
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception 
	 */
	public int deleteBook(Connection con,String id) throws Exception {
		String sql="delete from book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
	}
	public boolean exitBookByBookTypeId(Connection con,String id) throws Exception {
		String sql="select * from book where bookTypeid=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,id);
		ResultSet rs=pstmt.executeQuery();
		return rs.next();
	}
}
